RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / public_html / admin / app / V2 / Repositories /
Filename : TaskRepository.php
back
<?php

namespace App\V2\Repositories;

use App\Models\Task;
use App\Models\TaskAssigned;
use App\V2\Dtos\FilterTaskDTO;
use App\V2\Dtos\SearchDTO;
use App\V2\Dtos\TaskAssigneeFilterDTO;
use App\V2\Dtos\TaskFilterDto;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

/**
 * Handles the data access logic for Task related tables
 */
class TaskRepository extends BaseRepository {

    public static $TASK_TABLE_NAME = "tasks";
    public static $TASK_SETTINGS_TABLE_NAME = "task_settings";
    public static $TASK_ASSIGNED_TABLE_NAME = "task_assigned";
    public static $TASK_VISIBILITY_TABLE_NAME = "task_visibility";
    public static $TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS = "visibility_country_single";
    public static $TASK_VISIBILITY_SINGLE_COUNTRY_TABLE_NAME = "task_visibility AS visibility_country_single";

    /**
     * Joins with following table:
     * tasks
     * -> left join task_settings
     *
     * Applied following filter conditions:
     * --> tasks->group_id or task_settings->global
     */
    public function filterTasks(FilterTaskDTO $filterDto){

        $user = $filterDto->getCurrentUser();
        $user_zipcode = $user->userAddress != null ? $user->userAddress->zipcode : null;
        $user_country = $user->userAddress != null ? $user->userAddress->country_name : null;

        //start of joins
        $queryBuilder = Task::query()
            ->select('tasks.*')
            ->leftJoin(TaskRepository::$TASK_SETTINGS_TABLE_NAME, function ($join) {
                $join->on(TaskRepository::$TASK_TABLE_NAME.'.task_id',
                        '=', TaskRepository::$TASK_SETTINGS_TABLE_NAME .'.task_id')
                    // ->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                    //     array("'GLOBAL'","'GLOBAL_FOR_PARENT'","'GLOBAL_FOR_PARENT_AND_CHILD'",
                    //     "'ZIPCODE_PARENT'","'ZIPCODE_PARENT_AND_CHILD'","'SELECTED_PARENT'")
                    // )
                    ;
            });

        if($filterDto->getForGroupOrGlobal()){
            $queryBuilder->leftJoin(TaskRepository::$TASK_VISIBILITY_TABLE_NAME, function($join) use ($user, $user_zipcode, $user_country){
                $join->on(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.task_settings_id',
                    '=', TaskRepository::$TASK_SETTINGS_TABLE_NAME .'.task_settings_id');
            });
            $queryBuilder->leftJoin(TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_TABLE_NAME, function($join) use ($user, $user_zipcode, $user_country){
                $join->on(TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS.'.task_settings_id',
                    '=', TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS .'.task_settings_id');
                $join->on(TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS.'.entity_type', '=' ,DB::raw("'COUNTRY_SINGLE'"));
            });
        }

        //end of joins

        //non global filter for parent
        if($filterDto->getForGroupOrGlobal() != true && $filterDto->getForGroup() != false){
            $queryBuilder->where(TaskRepository::$TASK_TABLE_NAME . '.group_id' ,'=', $filterDto->getCurrentGroupId());
        }

        //start of global filters for parents.
        if($filterDto->getRequiredFor() == 'PARENT'){
            if($filterDto->getForGroupOrGlobal()){
                //applying group_id or global filter
                $queryBuilder->where(function  ($query) use($filterDto, $user, $user_zipcode, $user_country) {
                    $query->where( TaskRepository::$TASK_TABLE_NAME . '.group_id', '=', $filterDto->getCurrentGroupId())
                        ->orWhere(function  ($query) use($filterDto) {
                            $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                                array('GLOBAL','GLOBAL_FOR_PARENT','GLOBAL_FOR_PARENT_AND_CHILD',) );
                        });
                    $query->orWhere(function ($query) use($user, $user_zipcode,$user_country){
                        $query->where(function ($query) use($user, $user_zipcode){
                            $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                                array('SELECTED_PARENT') );
                            $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_type', 'USER');
                            $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_value', $user->user_id);
                        });
                        if($user_zipcode != null) {
                            $query->orWhere(function ($query) use($user, $user_zipcode, $user_country){
                                $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                                    array('ZIPCODE_PARENT','ZIPCODE_PARENT_AND_CHILD') );
                                $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_type', 'ZIPCODE');
                                $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_value', $user_zipcode);
                                $query->where(TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS.'.entity_value', $user_country);
                            });
                        }
                        if($user_country != null) {
                            $query->orWhere(function ($query) use($user, $user_country){
                                $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                                    array('COUNTRY_PARENT','COUNTRY_PARENT_AND_CHILD') );
                                $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_type', 'COUNTRY');
                                $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_value', $user_country);
                            });
                        }
                    });

                });
            }
        }
        //end of global filters for parent

        //start of filters for child
        if($filterDto->getRequiredFor() == 'CHILD'){
            $queryBuilder->leftJoin(TaskRepository::$TASK_ASSIGNED_TABLE_NAME ,
                function ($join) use($filterDto) {
                    $join->on(TaskRepository::$TASK_TABLE_NAME.'.task_id',
                        '=', TaskRepository::$TASK_ASSIGNED_TABLE_NAME.'.task_id')
                        ->where(TaskRepository::$TASK_ASSIGNED_TABLE_NAME.'.user_id',
                        '=', $filterDto->getCurrentUserId())
                        ->where(TaskRepository::$TASK_ASSIGNED_TABLE_NAME.'.group_id',
                        '=', $filterDto->getCurrentGroupId());
                });

            if($filterDto->getForGroupOrGlobal()){
                $queryBuilder->where(function ($query) use($filterDto, $user, $user_zipcode, $user_country){
                    $query->whereNotNull(TaskRepository::$TASK_ASSIGNED_TABLE_NAME.'.user_id');
                    $query->orWhere(function ($query) use($user, $user_zipcode,$user_country){
                        $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                            array('ZIPCODE_PARENT_AND_CHILD') );
                        $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_type', 'ZIPCODE');
                        $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_value', $user_zipcode);
                        $query->where(TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS.'.entity_value', $user_country);
                    });
                    if($user_country != null){
                        $query->orWhere(function ($query) use($user, $user_country){
                            $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                                array('COUNTRY_PARENT_AND_CHILD') );
                            $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_type', 'COUNTRY');
                            $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_value', $user_country);
                        });
                    }
                    $query->orWhere(function  ($query) use($filterDto, $user, $user_zipcode) {
                        $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                            array('GLOBAL', 'GLOBAL_FOR_PARENT_AND_CHILD') );
                    });
                });
            } else {
                $queryBuilder->whereNotNull(TaskRepository::$TASK_ASSIGNED_TABLE_NAME.'.user_id');
            }
        }
        //end of filter for child

        if($filterDto->getCreatedByIds()){
            $queryBuilder->whereIn(TaskRepository::$TASK_TABLE_NAME . '.created_by' , $filterDto->getCreatedByIds());
        }

        if($filterDto->getDueByLessThan() != null) {
            $due_by_date = $filterDto->getDueByLessThan();
            if($filterDto->getIncludeNullForDueBy()){
                $queryBuilder->where(function($query) use($due_by_date){
                    $query->where(TaskRepository::$TASK_TABLE_NAME. '.due_by', '<=', DB::raw("'$due_by_date'"));
                    $query->orWhereNull(TaskRepository::$TASK_TABLE_NAME. '.due_by');

                });
            } else{
                $queryBuilder->where(TaskRepository::$TASK_TABLE_NAME. '.due_by', '<=', DB::raw("'$due_by_date'"));
            }
        }

        if($filterDto->getDueByGraterThan() != null) {
            $due_by_date = $filterDto->getDueByGraterThan();
            if($filterDto->getIncludeNullForDueBy()){
                $queryBuilder->where(function($query) use($due_by_date){
                    $query->where(TaskRepository::$TASK_TABLE_NAME. '.due_by', '>', DB::raw("'$due_by_date'"));
                    $query->orWhereNull(TaskRepository::$TASK_TABLE_NAME. '.due_by');
                });
            } else{
                $queryBuilder->where(TaskRepository::$TASK_TABLE_NAME. '.due_by', '>', DB::raw("'$due_by_date'"));
            }
        }

        if($filterDto->shouldBeRecommended() != null){
            $queryBuilder->where(TaskRepository::$TASK_TABLE_NAME. '.is_featured', '=', $filterDto->shouldBeRecommended());
        }

        if($filterDto->shouldBeRecurring() != null && $filterDto->shouldBeRecurring()) {
            $queryBuilder->where(TaskRepository::$TASK_SETTINGS_TABLE_NAME.".repeate_after_days" , ">", "0");
        }

        if($filterDto->getTaskIds() != null) {
            $queryBuilder->whereIn(TaskRepository::$TASK_TABLE_NAME. '.task_id', $filterDto->getTaskIds() );
        }

        if($filterDto->getProductIds() != null){
            $queryBuilder->whereIn(TaskRepository::$TASK_TABLE_NAME.'.product_id', $filterDto->getProductIds());
        }

        if($filterDto->getGoalId() != null){
            $queryBuilder->where(TaskRepository::$TASK_TABLE_NAME.'.goal_id', $filterDto->getGoalId());
        }

        if($filterDto->shouldNotHaveProduct() != null){
            if($filterDto->shouldNotHaveProduct() == true){
                $queryBuilder->whereNotNull(TaskRepository::$TASK_TABLE_NAME.'.product_id');
            } else {
                $queryBuilder->whereNull(TaskRepository::$TASK_TABLE_NAME.'.product_id');
            }
        }

        //Sorting and ordering
        $order_by_column = 'task_id';
        $order = 'DESC';

        if($filterDto->getOrder() != null){
            $order = $filterDto->getOrder();
        }

        if($filterDto->getOrderByColumn() != null){
            $order_by_column = $filterDto->getOrderByColumn();
        }

        //setting ordering for child, returning all the tasks assigned to user first.
        if($filterDto->getRequiredFor() == 'CHILD'){
            $queryBuilder->orderBy(TaskRepository::$TASK_ASSIGNED_TABLE_NAME.'.user_id', 'DESC');
        }
        $queryBuilder->orderBy($order_by_column, $order);

        $queryBuilder->groupBy(TaskRepository::$TASK_TABLE_NAME.'.task_id');

        //limits and pagination
        if($filterDto->getLimit() != null)
            $queryBuilder->limit($filterDto->getLimit());

        Log::debug("Task Sql:" . $queryBuilder->toSql());
        Log::debug("Values for Sql:" . print_r($queryBuilder->getBindings(), true));
        if($filterDto->getOnlyCount() != null && $filterDto->getOnlyCount() == true)
            return $queryBuilder->count();
        else if($filterDto->getPageNo() != null)
            return $queryBuilder->paginate($filterDto->getLimit(), ['*'], 'page', $filterDto->getPageNo());
        else
            return $queryBuilder->get();
    }

    public function getTaskAssigneesInGroup(TaskAssigneeFilterDTO $filterDto){

        $queryBuilder = TaskAssigned::query();
        $taskAssignee =  $queryBuilder->whereIn('task_id', $filterDto->getTaskIds())
            ->where('group_id', $filterDto->getCurrentGroupId())
            ->get();

        return $taskAssignee;
    }

    public function search(SearchDTO $searchDto){
        $user = $searchDto->getCurrentUser();
        $user_zipcode = $user->userAddress != null ? $user->userAddress->zipcode : null;
        $user_country = $user->userAddress != null ? $user->userAddress->country_name : null;

        //start of joins
        $queryBuilder = Task::query()
            ->select('tasks.*')
            ->leftJoin(TaskRepository::$TASK_SETTINGS_TABLE_NAME, function ($join) {
                $join->on(TaskRepository::$TASK_TABLE_NAME.'.task_id',
                        '=', TaskRepository::$TASK_SETTINGS_TABLE_NAME .'.task_id');
            }
        );
        $queryBuilder->leftJoin(TaskRepository::$TASK_VISIBILITY_TABLE_NAME, function($join) use ($user, $user_zipcode, $user_country){
                $join->on(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.task_settings_id',
                    '=', TaskRepository::$TASK_SETTINGS_TABLE_NAME .'.task_settings_id');
            }
        );
        $queryBuilder->leftJoin(TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_TABLE_NAME, function($join) use ($user, $user_zipcode, $user_country){
                $join->on(TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS.'.task_settings_id',
                    '=', TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS .'.task_settings_id');
                $join->on(TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS.'.entity_type', '=' ,DB::raw("'COUNTRY_SINGLE'"));
            }
        );

        $required_for = $searchDto->getRequiredFor();
        if($required_for == 'PARENT'){
            $queryBuilder->where(function  ($query) use($searchDto, $user, $user_zipcode, $user_country) {
                $query->where( TaskRepository::$TASK_TABLE_NAME . '.group_id', '=', $searchDto->getCurrentGroupId())
                    ->orWhere(function  ($query) use($searchDto) {
                        $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                            array('GLOBAL','GLOBAL_FOR_PARENT','GLOBAL_FOR_PARENT_AND_CHILD',) );
                    });
                $query->orWhere(function ($query) use($user, $user_zipcode){
                    $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                        array('SELECTED_PARENT') );
                    $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_type', 'USER');
                    $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_value', $user->user_id);
                });
                if($user_zipcode != null) {
                    $query->orWhere(function ($query) use($user, $user_zipcode, $user_country){
                        $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                            array('ZIPCODE_PARENT','ZIPCODE_PARENT_AND_CHILD') );
                        $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_type', 'ZIPCODE');
                        $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_value', $user_zipcode);
                        $query->where(TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS.'.entity_value', $user_country);
                    });
                }
                if($user_country != null) {
                    $query->orWhere(function ($query) use($user, $user_country){
                        $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                            array('COUNTRY_PARENT','COUNTRY_PARENT_AND_CHILD') );
                        $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_type', 'COUNTRY');
                        $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_value', $user_country);
                    });
                }
            });
        }
        if($required_for == 'CHILD'){
            $queryBuilder->leftJoin(TaskRepository::$TASK_ASSIGNED_TABLE_NAME ,
            function ($join) use($searchDto) {
                $join->on(TaskRepository::$TASK_TABLE_NAME.'.task_id',
                    '=', TaskRepository::$TASK_ASSIGNED_TABLE_NAME.'.task_id')
                    ->where(TaskRepository::$TASK_ASSIGNED_TABLE_NAME.'.user_id',
                    '=', $searchDto->getCurrentUserId());
            });

            $queryBuilder->where(function ($query) use($searchDto, $user, $user_zipcode, $user_country){
                $query->whereNotNull(TaskRepository::$TASK_ASSIGNED_TABLE_NAME.'.user_id');
                $query->orWhere(function  ($query) use($searchDto, $user, $user_zipcode) {
                    $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                        array('GLOBAL', 'GLOBAL_FOR_PARENT_AND_CHILD') );
                });
                if($user_zipcode != null) {
                    $query->orWhere(function ($query) use($user, $user_zipcode,$user_country){
                        $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                            array('ZIPCODE_PARENT_AND_CHILD') );
                        $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_type', 'ZIPCODE');
                        $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_value', $user_zipcode);
                        $query->where(TaskRepository::$TASK_VISIBILITY_SINGLE_COUNTRY_ALIAS.'.entity_value', $user_country);
                    });
                }
                if($user_country != null){
                    $query->orWhere(function ($query) use($user, $user_country){
                        $query->whereIn(TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.visible_to',
                            array('COUNTRY_PARENT_AND_CHILD') );
                        $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_type', 'COUNTRY');
                        $query->where(TaskRepository::$TASK_VISIBILITY_TABLE_NAME.'.entity_value', $user_country);
                    });
                }
            });
        }

        $queryBuilder->where(function ($query) use($searchDto){
            $query->where(TaskRepository::$TASK_TABLE_NAME.'.task_title', 'LIKE', DB::raw("'%".$searchDto->getQ()."%'"))
            ->orWhere(TaskRepository::$TASK_TABLE_NAME.'.description', 'LIKE', DB::raw("'%".$searchDto->getQ()."%'"));
        });

        $paginatedTasks = $queryBuilder->groupBy(TaskRepository::$TASK_TABLE_NAME.'.task_id')
                   ->paginate($searchDto->getLimit(), ['*'], 'page', $searchDto->getPageNo());
        return $paginatedTasks;
    }

    public function getTaskAssignedForUser(TaskAssigneeFilterDTO $filterDto){

        $queryBuilder = TaskAssigned::query();
        $queryBuilder->whereIn('task_id', $filterDto->getTaskIds())
                            ->where('user_id', $filterDto->getCurrentUserId());
        $taskAssigned = $queryBuilder->get();

        return $taskAssigned;
    }

    public function getAllRepeatableTasksForToday() {
        $queryBuilder = Task::query()
            ->select('tasks.task_id', 'tasks.*')
            ->leftJoin(TaskRepository::$TASK_SETTINGS_TABLE_NAME, function ($join) {
                $join->on(TaskRepository::$TASK_TABLE_NAME.'.task_id',
                        '=', TaskRepository::$TASK_SETTINGS_TABLE_NAME .'.task_id')
                    ;
            });
        $queryBuilder->whereRaw('MOD(DATEDIFF(CURRENT_DATE, '.TaskRepository::$TASK_TABLE_NAME.'.created_at), '.
            TaskRepository::$TASK_SETTINGS_TABLE_NAME.'.repeate_after_days) = 0');
        $queryBuilder->where(TaskRepository::$TASK_TABLE_NAME.'.is_repeated',false);
        $queryBuilder->where(TaskRepository::$TASK_TABLE_NAME.'.due_by' , '>=' , DB::raw('CURRENT_DATE'));
        Log::debug("Tasks to be repeated SQL: ". $queryBuilder->toSql());

        $tasks = $queryBuilder->get();

        return $tasks;
    }
}
?>